if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetActiveInactiveProtocolIds]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetActiveInactiveProtocolIds]
GO

/************************************************************************************************                              
* Name  : GetActiveInactiveProtocolIds                                                                                       
* Purpose: This stored procedure retrieves the actives/ Inactive Protocol matching                         
*     the search criteria                            
*                                
* PARAMETERS                                
* Name   Description                              
* ------------------------- -----------------------------                              
* @domainuserid    -FrameworkDomainUserId                                
* @originatingpage   -Page Initiating the Procedure                              
* @protocolNo    -Protocol number(Search Criteria)                              
* @protocolTitle   -Protocol Title (Serach criteria)                              
* @sponsorName    -Spnosor name (Search criteria)                              
* @versionNo    -Verson number (Sercah criteria)                              
* @expDateFrom DateTime  -expiry Date From (Search Criteria)                              
* @expDateTo    -Expiry Date To (Search Criteria)                              
* @piId      -PI Id (Search criteria)                              
* @cosIds     -CosIds (Serach Criteria)                              
* @sortColumn    -SortColumn(SubmissionDate-Default)                        
* @sortOrder    -Sort Order for the result set (bit=1-->Desc,0-->Ascending)                        
*                              
************************************************************************************************                              
*Call: [GetActiveInactiveProtocolIds] 103100,0                              
************************************************************************************************/                                 
CREATE procedure [dbo].[GetActiveInactiveProtocolIds] --169570,1,'A'  
(                                
 @domainuserid int,                                
 @originatingpage int,            
 @module char(1)= 'H',                              
 @protocolNo varchar(12)= null,                              
 @protocolTitle varchar(1024)= null,                              
 @sponsorName varchar(75)= null,                              
 @versionNo varchar(100)= null,                              
 @expDateFrom DateTime = null,                              
 @expDateTo DateTime = null,                              
 @piId Int=null,                              
 @cosIds ntext = null,      
 @species varchar(256) = null,      
 @isRadioIsotope bit = 0,      
 @isBiologicalAgent bit = 0,      
 @isToxicChemicals bit = 0,      
 @isTissueTest bit = 0,                              
 @sortColumn varchar(150)= 'SubmissionDate',                                                    
 @sortOrder bit=1-->Asc,0-->Desc                                          
)                                 
as                                
begin                                
set nocount on                                
                                
 declare @activepage int                                
 declare @inactivepage int                                
                                
 set @activepage = 0                                
 set @inactivepage = 1                                
                                
 declare @activestatustypeid varchar(1)                                
 declare @holdstatustypeid varchar(1)                                
 declare @holdlapsedstatustypeid varchar(1)                                 
 declare @inactivestatustypeid varchar(1)                                
 declare @terminatedstatustypeid varchar(1)                                
                                 
 set @activestatustypeid = 1                                
 set @holdstatustypeid = 4                                
 set @holdlapsedstatustypeid = 9                                
 set @inactivestatustypeid = 2                                
 set @terminatedstatustypeid = 7                               
                              
                              
 if  @protocolNo is  null                             
  Set @protocolNo = '%'                              
 else                              
  Set @protocolNo = @protocolNo + '%'                              
                              
 if  @protocolTitle is null                              
  Set @protocolTitle = '%'              
 else                              
  Set @protocolTitle = @protocolTitle + '%'                 
                              
 if @sponsorName is null                              
  Set @sponsorName = '%'                              
 Else                               
  Set @sponsorName = @sponsorName + '%'                              
                              
 if @versionNo is null                              
  Set @versionNo =  '%'                              
 Else                               
  Set @versionNo = @versionNo + '%'       

  Set @species = IsNull (@species,'%') 
  set @expDateFrom = IsNull(@expDateFrom, '1/1/1900')
  set @expDateTo	 = IsNull(@expDateTo,'12/31/2099')                       
                              
  declare @protocolresourcetypeid varchar(1)                                
  select @protocolresourcetypeid = [Id] from FwkResourceType where [Name] = 'Protocol'                     
                                
  declare @keyGiverAccessLevel int
  declare @PrinciPalInvId varchar(20)                                
  set @keyGiverAccessLevel = 2   

  set @PrinciPalInvId = isNull(cast(@piId as varchar(20)),'%')                             
                                
  -- obtain the desired protocol statuses for the calling context                                
  declare @statustable table                                
  (                                 
   status int not null                                
  )                                
                                
  if @originatingpage = @activepage                                 
   begin                                
    insert into @statustable values (@activestatustypeid)                                
    if (@@error <> 0)                                
  begin                                
   raiserror('insert into @statustable failed', 16, 1)                                
   return                                
  end                                
    insert into @statustable values (@holdstatustypeid)                                
    if (@@error <> 0)                                
  begin                                
   raiserror('insert into @statustable failed', 16, 1)                                
   return                                
  end                                
    insert into @statustable values (@holdlapsedstatustypeid)                                
    if (@@error <> 0)                                
  begin                                
   raiserror('insert into @statustable failed', 16, 1)                                
   return                                
  end                                
   end                                
                                
  else if @originatingpage = @inactivepage                                 
   begin                                
    insert into @statustable values (@inactivestatustypeid)                                
    if (@@error <> 0)                                
  begin                                
   raiserror('insert into @statustable failed', 16, 1)                                
   return                                
  end                                
    insert into @statustable values (@terminatedstatustypeid)                                
    if (@@error <> 0)                                
  begin                                
   raiserror('insert into @statustable failed', 16, 1)                                
   return                                
  end                                
   end                                
                                
  else                                
   begin                                
    raiserror('wrong originating context', 16, 1)                                
    return                                
   end                             
                             
  if @cosIds is null                              
BEGIN                             
                          
 select
 protocol.[id] as ProtocolId
    from protocol                                
     inner join dbo.fnadmqgetauthorizedresourcelist(@domainuserid, @protocolresourcetypeid) ara                   
      on ara.resourceid = protocol.fwkresourceid                                 
     inner join ProtocolOverallStatusType status                                
      on protocol.OverallStatusId = status.[id]                                
     left join ProtocolSubStatusType substatus                                
      on protocol.SubStatusId = substatus.[id]                                
     left join admperson piperson                   
      on piperson.[id] = protocol.PIPersonId                                
     left join admsponsor sponsor                                
      on protocol.admsponsorid = sponsor.[id]           
     inner join @statustable stc                            
      on  protocol.OverallStatusId =  stc.status                              
    where  protocol.isCurrent = 1      
   and protocol.moduleType = @module
   and ISNull(protocol.ProtocolType,'*') not like 'PROTOCOLTYPE_ATISSUE'    
   and  ara.AccessLevel !=  @keyGiverAccessLevel                             
   and IsNull(protocol.Title,'*') like @protocolTitle                              
   and IsNull(protocol.version,'*') like @versionNo                              
   and IsNull(Protocol.protocolnumber,'*') like @protocolNo                              
   and ((IsNull(Protocol.ExpirationDate,'1/1/1900') >= @expDateFrom) and (IsNull(Protocol.ExpirationDate,'1/1/1900') <= @expDateTo))                              
   and IsNull(sponsor.Name,'*') like @sponsorName                              
   and IsNull(cast(protocol.PiPersonId as varchar(20)),'*') like @PrinciPalInvId                             
   and IsNull(protocol.SpeciesCode , '*') like @species      
   and (protocol.RadioIsotope = @isRadioIsotope    
        or 0 = @isRadioIsotope)    
   and (protocol.BiologicalAgent = @isBiologicalAgent    
        or 0 = @isBiologicalAgent)    
   and (protocol.ToxicChem = @isToxicChemicals    
        or 0 = @isToxicChemicals)    
   and (protocol.TissueTest = @isTissueTest    
        or 0 = @isTissueTest)    
                               
     Order by CASE @SortOrder                              
     When 0 then                                
      CASE @SortColumn                               
    WHEN 'Title' THEN Protocol.[Title]                                        
    When 'PIName' Then piperson.[lastname]                              
    When 'SponsorName' Then Sponsor.[name]                               
    When 'OverallStatus' Then Status.[name]+','+substatus.[Name]                                                                                            
    When 'ProtocolNumber' THEN protocol.[ProtocolNumber]                              
    --Else Protocol.Title                              
    END                      
      END Desc,                       
     CASE @SortOrder                              
      When 0 then                        
      CASE @SortColumn                        
    When 'SubmissionDate' Then Protocol.[SubmissionDate]                               
    When 'ExpirationDate' Then Protocol.[ExpirationDate]                               
    ELSE Protocol.[SubmissionDate]                        
    END                                   
     END Desc,                              
      CASE @SortOrder                              
   When 1 then                               
      CASE @SortColumn                            
     WHEN 'Title' THEN Protocol.[Title]                                        
     When 'PIName' Then piperson.[lastname]                          
     When 'SponsorName' Then Sponsor.[name]                               
     When 'OverallStatus' Then Status.[name]+','+substatus.[Name]                                                                                              
     When 'ProtocolNumber' THEN protocol.[ProtocolNumber]                              
     --Else Protocol.Title                              
      END                        
     END ASC,                      
    CASE @SortOrder                        
     When 1 then                        
      case @SortColumn                                
     When 'SubmissionDate' Then Protocol.[SubmissionDate]                               
     When 'ExpirationDate' Then Protocol.[ExpirationDate]                               
     ELSE Protocol.[SubmissionDate]                      
      END                                  
   END                           
  END                        
  Else                              
  BEGIN                              
     --Filter the results based on the CosIds along with other search criteria if exists                              
    declare @CosIdtable table              
  (                                 
   cosId int not null                                
  )                                
    Insert into @CosIdtable Select value from [GetValueTableOfDelimittedString](@cosIds,',')                             
                                  
     select                              
    protocol.[id] as ProtocolId                            
    from protocol                                
     inner join dbo.fnadmqgetauthorizedresourcelist(@domainuserid, @protocolresourcetypeid) ara                                 
     on ara.resourceid = protocol.fwkresourceid                                 
    inner join ProtocolOverallStatusType status                                
     on protocol.OverallStatusId = status.[id]                                
    left join ProtocolSubStatusType substatus                                
     on protocol.SubStatusId = substatus.[id]                   
    inner join admperson piperson                                
     on piperson.[id] = protocol.PIPersonId                                
    left join admsponsor sponsor                                
     on protocol.admsponsorid = sponsor.[id] 
    Inner Join @CosIdtable costable                              
     On  costable.cosId = protocol.PIFwkDomainOrganizationId                           
    inner join @statustable stc                            
     On  protocol.OverallStatusId =  stc.status                               
   where  protocol.isCurrent = 1    
  and  ara.AccessLevel !=  @keyGiverAccessLevel     
 and protocol.moduleType = @module     
	and ISNull(protocol.ProtocolType,'*') not like 'PROTOCOLTYPE_ATISSUE'                                            
    and IsNull(protocol.Title,'%') like @protocolTitle                  
    and IsNull(protocol.version,'%') like @versionNo                              
    and IsNull(Protocol.protocolnumber,'%') like @protocolNo                              
    and ((IsNull(Protocol.ExpirationDate,'1/1/1900') >= @expDateFrom) and (IsNull(Protocol.ExpirationDate,'1/1/1900') <= @expDateTo))                              
    and IsNull(sponsor.Name,'%') like @sponsorName                              
    and cast(protocol.PiPersonId as varchar(20)) like @PrinciPalInvId       
   and IsNull(protocol.SpeciesCode , '*') like @species     
   and (protocol.RadioIsotope = @isRadioIsotope    
        or 0 = @isRadioIsotope)    
   and (protocol.BiologicalAgent = @isBiologicalAgent    
        or 0 = @isBiologicalAgent)    
   and (protocol.ToxicChem = @isToxicChemicals    
        or 0 = @isToxicChemicals)    
   and (protocol.TissueTest = @isTissueTest    
        or 0 = @isTissueTest)    
                          
   Order by CASE @SortOrder                              
     When 0 then                                
     CASE @SortColumn                               
   WHEN 'Title' THEN Protocol.[Title]                                        
   When 'PIName' Then piperson.[lastname]                              
   When 'SponsorName' Then Sponsor.[name]                               
   When 'OverallStatus' Then Status.[name]+','+substatus.[Name]                                                                                             
   When 'ProtocolNumber' THEN protocol.[ProtocolNumber]                              
   --Else Protocol.Title                              
   END                      
     END Desc,                       
    CASE @SortOrder                              
     When 0 then                        
     CASE @SortColumn                               
   When 'SubmissionDate' Then Protocol.[SubmissionDate]                               
   When 'ExpirationDate' Then Protocol.[ExpirationDate]                               
   ELSE Protocol.[SubmissionDate]                        
   END                                   
    END Desc,                              
     CASE @SortOrder                              
    When 1 then                               
   CASE @SortColumn                               
     WHEN 'Title' THEN Protocol.[Title]                                        
     When 'PIName' Then piperson.[lastname]                          
     When 'SponsorName' Then Sponsor.[name]                            
     When 'OverallStatus' Then Status.[name]+','+substatus.[Name]                                                                                              
     When 'ProtocolNumber' THEN protocol.[ProtocolNumber]                              
     --Else Protocol.Title               
   END                             
     END ASC,                      
   CASE @SortOrder                        
    When 1 then                        
     case @SortColumn                                
    When 'SubmissionDate' Then Protocol.[SubmissionDate]                               
    When 'ExpirationDate' Then Protocol.[ExpirationDate]                               
    ELSE Protocol.[SubmissionDate]                      
     END                                  
    END                          
  END                     
END 
GO



